﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ASG.CodeGenerator.SQL.TSQL
{
    public class SQLServerCode : DBMServerCode<SQLDatabaseCode, SqlConnection, SQLTableCode, SQLColumnCode, SQLProcedureCode, SQLParameterCode, SqlParameter>
    {
        public SQLServerCode() : base(DBMServersTypes.SQLServer) { }

        public SQLServerCode(string name)
            : base(DBMServersTypes.SQLServer)
        {
            Name = name;
            Type = DBMServersTypes.SQLServer;
        }

        public override List<SQLDatabaseCode> GetDatabasesInfo(IDBMServerCredentials<SqlConnection> credentials)
        {
            List<SQLDatabaseCode> databasesInfo = new List<SQLDatabaseCode>();
            SqlConnection connection = credentials.GetDBConnection(Name);
            try
            {
                connection.Open();
                DataTable databases = connection.GetSchema("Databases");
                connection.Close();

                foreach (DataRow row in databases.Rows)
                {
                    SQLDatabaseCode info = new SQLDatabaseCode();
                    info.Parent = this;
                    info.DatabaseID = (Int16)row["Dbid"];
                    info.Name = row["database_name"].ToString();
                    info.CreationDate = (DateTime)row["create_date"];
                    //Get Tables Info.
                    GetTables(connection, info);
                    databasesInfo.Add(info);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                    connection.Close();
            }
            return databasesInfo;
        }

        private void GetTables(SqlConnection connection, SQLDatabaseCode database)
        {
            //Set GetSchema restrictions to return only tables.
            string[] restrictions = new string[4];
            restrictions[0] = database.Name;
            restrictions[3] = "BASE TABLE";

            connection.Open();
            DataTable tables = connection.GetSchema("Tables", restrictions);
            connection.Close();

            foreach (DataRow row in tables.Rows)
            {
                SQLTableCode info = new SQLTableCode();
                info.Parent = database;
                info.Name = row["table_name"].ToString();
                database.Tables.Add(info);
            }

        }

        public override string GenerateCode()
        {
            throw new NotImplementedException();
        }
    }
}
